<?php 
require_once dirname(__FILE__)."/../config/dbconn.php";  

class Posts extends Dbconn {
    //выборка тайтла, имени, контента, даты создания
    //имени категории и создавшего юзера
    function show_posts($start, $num){
        $sql = Posts::getConnect()->query("
                 SELECT p.`id`,
                        p.`title`,
                        p.`name`,
                        p.`content`,
                        p.`visits`,
                        p.`date_create`, 
                        p.`short`, 
                        c.`cat_name`, 
                        u.`login`
                 FROM posts AS p, 
                      category AS c, 
                      users AS u
                 WHERE p.`id_category` = c.`id` AND 
                       p.`id_user` = u.`id` AND
                       p.`visible` = 1
                 ORDER BY `id` DESC
                 LIMIT ".intval($start).",
                       ".intval($num));
       // $sql->bindValue(1, $start, PDO::PARAM_INT);
       // $sql->bindValue(2, $num, PDO::PARAM_INT);
        //$sql->execute();
        return $sql;
    }
    
    //добавление поста(декспри, кейвордс, тайтл, имя, контент, категория, 
    //имя пользователя)+дата создания
    function add_post($user_login, $cat_name, $visible, $description, $keywords, $title, $name, $short, $content){
        $dt = date('H:i:s d F Y');
        $sql = "INSERT INTO `posts`(
                                  id_category, 
                                  id_user, 
                                  description, 
                                  keywords, 
                                  title, 
                                  name, 
                                  short, 
                                  content,
                                  visible,
                                  date_create               
                                  )
                SELECT category.`id`, 
                       users.`id`,
                       :description, 
                       :keywords, 
                       :title, 
                       :name, 
                       :short, 
                       :content,
                       :visible,
                       :dt 
                FROM category, 
                     users
                WHERE category.`cat_name` = :cat_name AND
                      users.`login` = :user_login";
                       
        $db = Posts::getConnect()->prepare($sql);
        $db->bindParam(':user_login', $user_login);
        $db->bindParam(':cat_name', $cat_name);
        $db->bindParam(':description', $description);
        $db->bindParam(':keywords', $keywords);
        $db->bindParam(':title', $title);
        $db->bindParam(':short', $short);
        $db->bindParam(':name', $name);
        $db->bindParam(':content', $content);
        $db->bindParam(':visible', $visible);
        $db->bindParam(':dt', $dt);
        $db->execute();
    }
    
    //редактирование поста(категория, тайтл, имя, контент) 
    //+дата редактирования
    function update_post($id, $cat_name, $visible, $description, $keywords, $title, $name, $short, $content){
        $dt = date('H:i:s d F Y');
        $sql = "UPDATE posts AS p
                SET p.`id_category` = (
                  SELECT category.`id` 
                  FROM category
                  WHERE category.`cat_name` = :cat_name),
                        p.`description` = :description,
                        p.`keywords` = :keywords,
                        p.`title` = :title,
                        p.`name` = :name,
                        p.`short` = :short,
                        p.`content` = :content,
                        p.`visible` = :visible,
                        p.`date_modify` = :dt    
                WHERE p.`id` = :id";
        $db = Posts::getConnect()->prepare($sql);
        $db->bindParam(':id', intval($id), PDO::PARAM_INT); 
        $db->bindParam(':cat_name', $cat_name);
        $db->bindParam(':description', $description);
        $db->bindParam(':keywords', $keywords);
        $db->bindParam(':title', $title);
        $db->bindParam(':short', $short);
        $db->bindParam(':name', $name);
        $db->bindParam(':content', $content);
        $db->bindParam(':visible', $visible);
        $db->bindParam(':dt', $dt);
        $db->execute();
    }
    
    //удаление выбранного поста   
    function delete_post($id){
        $sql = "DELETE FROM posts
                WHERE id= :id";
        
        $db = Posts::getConnect()->prepare($sql);
        $db->bindParam(':id', $id, PDO::PARAM_INT);
        $db->execute();        
        mysql_query($sql);
    }
    
    //выборка данных для редактирования поста
    function show_select_posts($id){
        $sql = "SELECT p.`id`,
                       p.`description`,
                       p.`keywords`,
                       p.`title`, 
                       p.`name`, 
                       p.`content`, 
                       p.`date_create`,
                       p.`short`, 
                       c.`cat_name`, 
                       u.`login`
                FROM posts AS p,
                     category AS c, 
                     users AS u
                WHERE p.`id_category` = c.`id` AND 
                      p.`id_user` = u.`id`     AND 
                      p.`id` = :id";      
        
        $db = Posts::getConnect()->prepare($sql);
        $db->bindParam(':id', $id);
        $db->execute();
        return $db;
    }
    
    //выборка всех записей выбранной категории
    function show_cat_posts($cat_id){
        $sql = "SELECT p.`id`, 
                       p.`title`, 
                       p.`name`, 
                       p.`content`, 
                       p.`date_create`,
                       p.`short`, 
                       c.`cat_name`, 
                       u.`login`
                FROM posts AS p, 
                     category AS c, 
                     users AS u
                WHERE p.`id_category` = c.`id` AND 
                      p.`id_user` = u.`id`     AND 
                      p.`id_category` = :cat_id
                ORDER BY id DESC";
                
        $db = Posts::getConnect()->prepare($sql);
        $db->bindParam('cat_id', $cat_id, PDO::PARAM_INT);
        $db->execute();
        return $db;                         
    }
    
    //получения кол-ва записей в таблице
    function posts_count(){
        $sql = Posts::getConnect()->query("
                 SELECT id 
                 FROM posts
                 ");
        return $sql;
    } 
    
    //выборка постов для админки
    function show_admin_posts($start, $num){
        $sql = "SELECT p.`id`, 
                       p.`title`, 
                       p.`name`, 
                       p.`content`, 
                       p.`date_create`,
                       p.`short`, 
                       c.`cat_name`, 
                       u.`login`
                FROM posts AS p, 
                     category AS c, 
                     users AS u
                WHERE p.`id_category` = c.`id` AND  
                      p.`id_user` = u.`id`
                ORDER BY `id` DESC
                LIMIT ".$start.", 
                        ".$num;                
        $db = Posts::getConnect()->query($sql);
        return $db;
    }
    
    //сортировка постов по выбранному значению
    function sort_posts($sort){
        $sql = "SELECT p.`id`, 
                       p.`title`, 
                       p.`name`, 
                       p.`content`, 
                       p.`date_create`, 
                       p.`short`, 
                       c.`cat_name`, 
                       u.`login`
                FROM posts AS p, 
                     category AS c, 
                     users AS u
                WHERE p.`id_category` = c.`id` AND  
                      p.`id_user` = u.`id`
                ORDER BY ".$sort." DESC";
        
        $db = Posts::getConnect()->query($sql);
        return $db;
    }
    
    //выборка ключевых слов & описаний
     function get_seo($id){
        $sql = "SELECT description, 
                       keywords 
                FROM posts
                WHERE id = :id";
        
        $db = Posts::getConnect()->prepare($sql);
        $db->bindParam(':id', intval($id), PDO::PARAM_INT);
        $db->execute();
        return $db; 
    } 
    
    //счетчик посещений
    function set_post_visits($id){
        $sql = "UPDATE posts
                SET visits = visits + 1
                WHERE id = :id";
       
        $db = Posts::getConnect()->prepare($sql);
        $db->bindParam(':id', intval($id), PDO::PARAM_INT);
        $db->execute();
    }
    
    //выборка 5 самых посещаемых постов
    function get_popular_posts(){
        $sql = Posts::getConnect()->query("
                 SELECT id,
                        name 
                 FROM posts
                 ORDER BY visits DESC
                 LIMIT 0, 5
                 ");
        
        return $sql;
    } 
    
    //выборка всех постов, которые содержат введенное значение
    function search_value($value){
        $sql = "SELECT id,
                       name,
                       short
                FROM posts
                WHERE name LIKE '%".$value."%' OR
                      short LIKE '%".$value."%' OR
                      content LIKE '%".$value."%'";
        
        $db = Posts::getConnect()->query($sql);
       // $db->bindParam(':value', $value);
       // $db->execute();
        return $db;        
    }
    
    function show_categories(){
        $sql = Posts::getConnect()->query("
                 SELECT id, 
                        cat_name, 
                        year
                 FROM category
                 ORDER BY id DESC");
        return $sql;
    }    
     
}
